import pandas as pd



def extract_by_year(file_path,sheet_name):
    # open the xlsx table2.1
    energy_con_df = pd.read_excel(file_path, sheet_name=sheet_name, usecols='B:U', skiprows=7, nrows=33)
    # Define the new column names
    columns = ["LA name", "LA code",
               "Road (CO2e)", "Diesel (CO2e)", "Electric (CO2e)", "Aviation (CO2e)",
               "Shipping (CO2e)", "Road (kWh)", "Rail Diesel (kWh)", "Rail Electric (kWh)",
               "Aviation (kWh)", "Shipping (kWh)", "Manufacturing Industries (tCO2)",
               "Construction (tCO2)", "Manufacturing Industries (tCH4)", "Construction (tCH4)",
               "Manufacturing Industries (tN2O)", "Construction (tN2O)",
               "Manufacturing Industries (Total tCO2e)", "Construction (Total tCO2e)"
               ]

    # Assign the new column names to the dataframe
    energy_con_df.columns = columns
    print(energy_con_df.columns)

    # table2.2
    aviation_df_columns = ["LA name", "LA code",
                           "t FU_ATF (Ground level_Heathrow)", 't FU_ATF (Elevated level_Heathrow)',
                           "t FU_Diesel(Airside vehicle_Heathrow)", "t FU_Gasoil(Airside vehicle_Heathrow)",
                           "t FU_HVO(Airside vehicle_Heathrow)", "t FU_Petrol(Airside vehicle_Heathrow)",  # Airside vehicle
                           "t FU_Diesel(Landside vehicle_Heathrow)", "t FU_Gasoil(Landside vehicle_Heathrow)",
                           # Landside vehicle
                           "Energy_Gas kWh(Stationary_Heathrow)", "Energy_Gasoil kWh(Stationary_Heathrow)",
                           "litres_FU_LPG(Stationary_Heathrow)", "t FU_Biomass(Stationary_Heathrow)",  # Stationary
                           "t FU_ATF(Ground level_City)", "t FU_ATF(Elevated level_City)", "t FU_Diesel(City)",
                           "t FU_Gasoil(City)", "t FU_HVO(City)", "t FU_Petrol(City)",
                           # City
                           "litres FU_Diesel(City)", "litres FU_Petrol(City)", "Energy_Gas (kWh)(City)",
                           "Energy_Gasoil (kWh)(City)", "litres FU_LPG(City)",
                           "t FU_ATF(Ground level Battersea)", "t FU_ATF(Elevated level Battersea)",  # Battersea
                           "t FU_ATF(Ground level Biggin Hill)", "t FU_ATF(Elevated level Biggin Hill)",  # Biggin Hill
                           "t FU_ATF(Ground level Northolt)", "t FU_ATF(Elevated level Northolt)",  # Northolt
                           "t FU_ATF(Elevated level Denham)",  # Denham
                           "t FU_ATF(Elevated level Elstree)",  # Elstree
                           "t FU_ATF(Elevated level Stapleford)",  # Stapleford
                           "Total(kWh)"
                           ]
    print(len(aviation_df_columns))
    aviation_df = pd.read_excel(file_path, sheet_name=sheet_name, usecols='B:AJ', skiprows=49, nrows=33)
    aviation_df.columns = aviation_df_columns
    print(aviation_df)

    # table2.3
    borough_columns = [
        "LA name", "LA code",
        "Ground level", "Elevated level", "Airside vehicle", "Landside vehicle", "Stationary",
        "Ground level", "Elevated level", "Airside vehicle", "Landside vehicle", "Stationary",
        "Ground level", "Elevated level", "Ground level", "Elevated level", "Ground level",
        "Elevated level", "Ground level", "Elevated level", "Ground level", "Elevated level",
        "Ground level", "Elevated level", "Ground level", "Elevated level", "Airside",
        "Landside", "Grand total"
    ]

    borough_df = pd.read_excel(file_path, sheet_name=sheet_name, usecols='B:AD', skiprows=93, nrows=33)
    borough_df.columns = borough_columns

    # table2.4
    ship_columns = [
        "LA name", "LA code",
        "kWh(Commercial_Shipping)",
        "tCO2(Commercial_Shipping)",
        "tCH4(Commercial_Shipping)",
        "tN2O(Commercial_Shipping)",
        "kWh(Passenger_Shipping)",
        "tCO2(Passenger_Shipping)",
        "tCH4(Passenger_Shipping)",
        "tN2O(Passenger_Shipping)",
        "Total kWh",
        "Total tCO2e"
    ]
    shipping_df = pd.read_excel(file_path, sheet_name=sheet_name, usecols='B:M', skiprows=137, nrows=33)
    shipping_df.columns = ship_columns
    print(shipping_df)

    # table2.5
    rail_columns = [
        "LA name",
        "LA code",
        "kWh(Passenger_Diesel_Rail)",
        "tCO2e(Passenger_Diesel_Rail)",
        "kWh(Passenger_Electric_Rail)",
        "tCO2e(Passenger_Electric_Rail)",
        "kWh(Passenger_Total_Rail)",
        "tCO2e(Passenger_Total_Rail)",
        "kWh(Freight_Diesel_Rail)",
        "tCO2e(Freight_Diesel_Rail)",
        "kWh(Freight_Electric_Rail)",
        "tCO2e(Freight_Electric_Rail)",
        "kWh(Freight_Total_Rail)",
        "tCO2e(Freight_Total_Rail)",
        "kWh(Rail_Total_Rail)",
        "tCO2e(Rail_Total_Rail)",
        "kWh(London Underground_TfL (Electric))",
        "tCO2e(London Underground_TfL (Electric))",
        "kWh(DLR (1)_TfL (Electric))",
        "tCO2e(DLR (2)_TfL (Electric))",
        "kWh(London Tram_TfL (Electric))",
        "tCO2e(London Tram_TfL (Electric))",
        "kWh(Total_TfL (Electric))",
        "tCO2e(Total_TfL (Electric))",
        "kWh(Diesel_Total)",
        "tCO2e(Diesel_Total)",
        "kWh(Electric_Total)",
        "tCO2e(Electric_Total)",
        "kWh(Overall_Total)",
        "tCO2eOverallTotal"
    ]
    print(len(rail_columns))
    rail_df = pd.read_excel(file_path, sheet_name=sheet_name, usecols='B:AE', skiprows=181, nrows=33)
    rail_df.columns = rail_columns
    print(rail_df)

    road_CO2_columns = [
        "LA name",
        "LA code",
        "tCO2(Motorcycle_Vehicle type (all vehicles excluding electric))",
        "tCO2(Taxi_Vehicle type (all vehicles excluding electric))",
        "tCO2(Car_Vehicle type (all vehicles excluding electric))",
        "tCO2(PHV_Vehicle type (all vehicles excluding electric))",
        "tCO2(LGV_Vehicle type (all vehicles excluding electric))",
        "tCO2(Bus and Coach_Vehicle type (all vehicles excluding electric))",
        "tCO2(Rigid_Vehicle type (all vehicles excluding electric))",
        "tCO2(Artic_Vehicle type (all vehicles excluding electric))",
        "tCO2(All vehicles excluding electric_Total)",
        "tCO2e - CH4(All vehicles excluding electric_Total)",
        "tCO2e - N2O (All vehicles excluding electric_Total)",
        "tCO2e(All vehicles excluding electric_Total)",
        "tCO2(Motorcycles_Vehicle type (electric))",
        "tCO2(Taxi_Vehicle type (electric))",
        "tCO2(Car_Vehicle type (electric))",
        "tCO2(PHV_Vehicle type (electric))",
        "tCO2(LGV_Vehicle type (electric))",
        "tCO2(Bus & Coach_Vehicle type (electric))",
        "tCO2(Rigid HGVs_Vehicle type (electric))",
        "tCO2(Articulated HGVs_Vehicle type (electric))",
        "tCO2(Total (electric)_Total)",
        "tCH4(Total (electric)_Total)",
        "tN2O(Total (electric)_Total)",
        "tCO2e(Total (electric)_Total)"
    ]


    road_CO2_df = pd.read_excel(file_path, sheet_name=sheet_name, usecols='B:AA', skiprows=225, nrows=33)

    road_CO2_df.columns = road_CO2_columns

    road_energy_columns = [
        "LA name",
        "LA code",
        "litres(Motorcycle (petrol)_Vehicle type (all vehicles excluding electric))",
        "litres(Taxi (diesel)_Vehicle type (all vehicles excluding electric))",
        "litres(Car (petrol)_Vehicle type (all vehicles excluding electric))",
        "litres(Car (diesel)_Vehicle type (all vehicles excluding electric))",
        "litres(PHV (petrol)_Vehicle type (all vehicles excluding electric))",
        "litres(PHV (diesel)_Vehicle type (all vehicles excluding electric))",
        "litres(LGV (petrol)_Vehicle type (all vehicles excluding electric))",
        "litres(LGV (diesel)_Vehicle type (all vehicles excluding electric))",
        "litres(Bus (diesel)_Vehicle type (all vehicles excluding electric))",
        "litres(Coach (diesel)_Vehicle type (all vehicles excluding electric))",
        "litres(Rigid (diesel)_Vehicle type (all vehicles excluding electric))",
        "litres(Artic (diesel)_Vehicle type (all vehicles excluding electric))",
        "Diesel litres(Total (all vehicles excluding electric))",
        "Petrol litres(Total (all vehicles excluding electric))",
        "Total litres(Total (all vehicles excluding electric))",
        "Diesel and petrol (kWh)(Total (all vehicles excluding electric))",
        "kWh(Motorcycles_Vehicle type (electric))",
        "kWh(Taxi_Vehicle type (electric))",
        "kWh(Car_Vehicle type (electric))",
        "kWh(PHV_Vehicle type (electric))",
        "kWh(LGV_Vehicle type (electric))",
        "kWh(Bus & Coach_Vehicle type (electric))",
        "kWh(Rigid HGVs_Vehicle type (electric))",
        "kWh(Articulated HGVs_Vehicle type (electric))",
        "Total electric (kWh)(Total)"
    ]

    print(len(road_energy_columns))
    road_energy_df = pd.read_excel(file_path, sheet_name=sheet_name, usecols='B:AB', skiprows=265, nrows=33)
    road_energy_df.columns = road_energy_columns


    output_files = [
        ("energy_con.csv", energy_con_df),
        ("aviation_data.csv", aviation_df),
        ("borough_data.csv", borough_df),
        ("shipping_data.csv", shipping_df),
        ("rail_data.csv", rail_df),
        ("road_CO2_data.csv", road_CO2_df),
        ("road_energy_data.csv", road_energy_df)
    ]

    # Save to CSV and collect file paths
    file_paths = []
    for file_name, df in output_files:
        file_path = f"2018/{file_name}"
        df.to_csv(file_path, index=False)
        file_paths.append(file_path)

if __name__ == '__main__':
    # Load the Excel file
    file_path = "LEGGI_2019_FINAL.xlsx"
    sheet_name = "II. Transport "
    extract_by_year(file_path,sheet_name)